from abc import ABC, abstractmethod
from typing import List
from io import BytesIO, TextIOWrapper
import base64

from urllib.parse import quote
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
import pydantic
import sqlalchemy as sa
from fastapi_pagination.bases import AbstractPage
from fastapi_pagination.ext.databases import paginate
from fastapi.responses import StreamingResponse
from fastapi import BackgroundTasks

from like.dependencies.database import db
from like.utils.urls import UrlUtil
from like.utils.array import ArrayUtil
from .models import hp_rules
from .schemas import (
    HpRulesListIn, HpRulesExportIn, HpRulesAddIn, HpRulesEditIn, HpRulesOut)
from .listsExcel import ListsExcel


class IHpRulesService(ABC):
    """首页规则抽象类"""

    @abstractmethod
    async def listBase(self, list_in: HpRulesListIn) -> sa.sql.Select:
        pass

    @abstractmethod
    async def list(self, list_in: HpRulesListIn) -> AbstractPage[HpRulesOut]:
        pass

    @abstractmethod    
    async def export_excel(self, export_in: HpRulesExportIn) -> str:
        pass

    @abstractmethod
    async def detail(self, id_: int) -> HpRulesOut:
        pass

    @abstractmethod
    async def add(self, add_in: HpRulesAddIn):
        pass

    @abstractmethod
    async def edit(self, edit_in: HpRulesEditIn):
        pass

    @abstractmethod
    async def delete(self, id_: int):
        pass


class HpRulesService(IHpRulesService):

    async def listBase(self, list_in: HpRulesListIn) -> sa.sql.Select:
        """首页规则基类"""
        columns = [
            hp_rules,
            
            ]
        where = []
        if list_in.sequence is not None and str(list_in.sequence):
            where.append(hp_rules.c.sequence == list_in.sequence)
        if list_in.sub_sequence is not None and str(list_in.sub_sequence):
            where.append(hp_rules.c.sub_sequence == list_in.sub_sequence)
        if list_in.category_id is not None and str(list_in.category_id):
            where.append(hp_rules.c.category_id == list_in.category_id)
        if list_in.types_id is not None and str(list_in.types_id):
            where.append(hp_rules.c.types_id == list_in.types_id)
        if list_in.source_id is not None and str(list_in.source_id):
            where.append(hp_rules.c.source_id == list_in.source_id)
        if list_in.short_content is not None and str(list_in.short_content):
            where.append(hp_rules.c.short_content.like(f'%{ list_in.short_content }%'))
        if list_in.comment is not None and str(list_in.comment):
            where.append(hp_rules.c.comment.like(f'%{ list_in.comment }%'))
        if list_in.regex is not None and str(list_in.regex):
            where.append(hp_rules.c.regex.like(f'%{ list_in.regex }%'))
        return sa.select(columns).where(*where) \
            .select_from(
                hp_rules
            ).order_by(hp_rules.c.id.asc())
    

    async def list(self, list_in: HpRulesListIn) -> AbstractPage[HpRulesOut]:
        """首页规则列表"""
        query = await self.listBase(list_in)
        res = await paginate(db, query)
        return res
    

    async def export_excel(self, export_in: HpRulesExportIn) -> str:
        """导出首页规则列表为 Excel 文件"""
        result = await self.listBase(export_in)

        # 计算要导出的记录范围
        offset = (export_in.page_start - 1) * export_in.page_size
        limit = (export_in.page_end - export_in.page_start + 1) * export_in.page_size

        # 修改查询以仅选择所需的记录
        result = result.offset(offset).limit(limit)

        query_result = await db.fetch_all(result)

        # 将查询结果转换为 Pandas DataFrame
        df = pd.DataFrame(query_result, columns=result.selected_columns.keys())

        # 将 DataFrame 转换为 Excel 文件的字节流
        excel_data = BytesIO()
        df.to_excel(excel_data, index=False)
        excel_data.seek(0)  # 将指针移回文件开头

        excel_bytes = excel_data.read()  # 读取字节流内容到字节字符串中
        return base64.b64encode(excel_bytes).decode('utf-8')  # 将字节字符串转换为Base64编码的字符串表示ArrayBuffer

        def iterfile():
            while True:
                data = excel_data.read(1024 * 1024)  # Read data in chunks of 1MB
                if not data:
                    break
                yield data

        # 返回 Excel 文件的字节流
        return StreamingResponse(
            # iter([excel_data.getvalue()]),
            iterfile(),
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charSet:UTF-8",
            headers={
                "Content-Disposition": f"attachment; filename*=UTF-8''{quote(export_in.file_name)}.xlsx"
            }
        )


    async def detail(self, id_: int) -> HpRulesOut:
        """首页规则详情"""
        model = await db.fetch_one(
            hp_rules.select().where(
                hp_rules.c.id == id_)
            .limit(1))
        assert model, '数据不存在!'
        res = HpRulesOut.from_orm(model)
        return res

    async def add(self, add_in: HpRulesAddIn):
        """首页规则新增"""
        await db.execute(hp_rules.insert().values({
            'sequence': add_in.sequence,
            'sub_sequence': add_in.sub_sequence,
            'category_id': add_in.category_id,
            'types_id': add_in.types_id,
            'source_id': add_in.source_id,
            'short_content': add_in.short_content,
            'comment': add_in.comment,
            'regex': add_in.regex,
        }))

    async def edit(self, edit_in: HpRulesEditIn):
        """首页规则编辑"""
        assert await db.fetch_one(
            hp_rules.select().where(
                hp_rules.c.id == edit_in.id)
            .limit(1)), '数据不存在!'
        await db.execute(
            hp_rules.update().where(hp_rules.c.id == edit_in.id).values({
                'sequence': edit_in.sequence,
                'sub_sequence': edit_in.sub_sequence,
                'category_id': edit_in.category_id,
                'types_id': edit_in.types_id,
                'source_id': edit_in.source_id,
                'short_content': edit_in.short_content,
                'comment': edit_in.comment,
                'regex': edit_in.regex,
            }))

    async def delete(self, id_: int):
        """首页规则删除"""
        assert await db.fetch_one(
            hp_rules.select().where(
                hp_rules.c.id == id_)
            .limit(1)), '数据不存在!'
        await db.execute(hp_rules.delete().where(hp_rules.c.id == id_))
        
    @classmethod
    async def instance(cls):
        """实例化"""
        return cls()
